from sshtunnel import SSHTunnelForwarder
import pymysql

def Student(Object):
    def __init__(self,no,name,sex,birth,address,colno):
        self.no=no
        self.name=name
        self.sex=sex
        self.birth=birth
        self.address=address
        self.colno=colno

    def __str__(self):
        return f'{self.no}\t{self.name}\t{self.sex}'

# 通过SSH连接云服务器
server = SSHTunnelForwarder(
    ssh_address_or_host=('101.132.42.244', 22),
    ssh_username='root',  # 云服务器登录账号admin
    ssh_password='Cws961112',  # 云服务器登录密码password
    remote_bind_address=('localhost', 3306)  # 数据库服务地址ip,一般为localhost和端口port，一般为3306
)

# 云服务器开启
server.start()
# 云服务器上mysql数据库连接
con = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                      port=server.local_bind_port,
                      user='root',  # mysql的登录账号admin
                      password='961112',  # mysql的登录密码pwd
                      db='school',  # mysql中要访问的数据表
                      charset='utf8',cursorclass=pymysql.cursors.DictCursor)  # 表的字符集
try:
# # 创建游标
    with con.cursor() as cur:
        # # 执行sql语句
            cur.execute('SELECT stuid as no,stuname as name,stusex as sex ,stubirth as birth,stuaddress as address ,colid as colno from tb_stduent')
            # # 读取数据
            data = cur.fetchall()
            # # 打印数据
            for row in data:
                print('学号',row['no'],end='\t')
                print('姓名',row['name'],end='\t')
                print('性别',row['sex'])
                # stud=Student(row)
                # print(stud)
except  pymysql.MySQLError as err:
        print(err)
finally:
    cur.close()   # # 游标、连接关闭
    con.close()
    server.close()   # # 云服务器关闭
